Imports System.Text
Imports System.Data.SqlClient

Public Class MSSQLParaHelper
    Implements Katze.IParaHelper

    Private paras As Collection

    Public Sub New()
        paras = New Collection
    End Sub


    Public Sub addBigInt(ByVal paraname As String, ByVal value As Object, Optional ByVal direction As System.Data.ParameterDirection = System.Data.ParameterDirection.Input) Implements IParaHelper.addBigInt
        If Not paraname.StartsWith("@") Then paraname = "@" & paraname

        Dim p As New SqlParameter(paraname, value)
        p.Direction = direction
        p.SqlDbType = SqlDbType.BigInt
        paras.Add(p, paraname)
    End Sub

    Public Sub addBinary(ByVal paraname As String, ByVal value() As Byte, Optional ByVal direction As System.Data.ParameterDirection = System.Data.ParameterDirection.Input) Implements IParaHelper.addBinary
        If Not paraname.StartsWith("@") Then paraname = "@" & paraname

        Dim p As New SqlParameter(paraname, value)
        p.Direction = direction
        p.SqlDbType = SqlDbType.Binary
        paras.Add(p, paraname)
    End Sub

    Public Sub addBit(ByVal paraname As String, ByVal value As Object, Optional ByVal direction As System.Data.ParameterDirection = System.Data.ParameterDirection.Input) Implements IParaHelper.addBit
        If Not paraname.StartsWith("@") Then paraname = "@" & paraname

        Dim p As New SqlParameter(paraname, value)
        p.Direction = direction
        p.SqlDbType = SqlDbType.Bit
        paras.Add(p, paraname)
    End Sub

    Public Sub addChar(ByVal paraname As String, ByVal value As Object, ByVal size As Integer, Optional ByVal direction As System.Data.ParameterDirection = System.Data.ParameterDirection.Input) Implements IParaHelper.addChar
        If Not paraname.StartsWith("@") Then paraname = "@" & paraname

        Dim p As New SqlParameter(paraname, value)
        p.SqlDbType = SqlDbType.Char
        p.Size = size
        p.Direction = direction
        paras.Add(p, paraname)
    End Sub

    Public Sub addDateTime(ByVal paraname As String, ByVal value As Object, Optional ByVal direction As System.Data.ParameterDirection = System.Data.ParameterDirection.Input) Implements IParaHelper.addDateTime
        If Not paraname.StartsWith("@") Then paraname = "@" & paraname
        Dim p As New SqlParameter(paraname, value)
        p.SqlDbType = SqlDbType.DateTime
        p.Direction = direction
        paras.Add(p, paraname)
    End Sub

    Public Sub addDecimal(ByVal paraname As String, ByVal value As Object, Optional ByVal direction As System.Data.ParameterDirection = System.Data.ParameterDirection.Input) Implements IParaHelper.addDecimal
        If Not paraname.StartsWith("@") Then paraname = "@" & paraname
        Dim p As New SqlParameter(paraname, value)
        p.SqlDbType = SqlDbType.Decimal
        p.Direction = direction
        paras.Add(p, paraname)
    End Sub

    Public Sub addInt(ByVal paraname As String, ByVal value As Object, Optional ByVal direction As System.Data.ParameterDirection = System.Data.ParameterDirection.Input) Implements IParaHelper.addInt
        If Not paraname.StartsWith("@") Then paraname = "@" & paraname

        Dim p As New SqlParameter(paraname, value)
        p.Direction = direction
        p.SqlDbType = SqlDbType.Int
        paras.Add(p, paraname)
    End Sub

    Public Sub addNChar(ByVal paraname As String, ByVal value As Object, ByVal size As Integer, Optional ByVal direction As System.Data.ParameterDirection = System.Data.ParameterDirection.Input) Implements IParaHelper.addNChar
        If Not paraname.StartsWith("@") Then paraname = "@" & paraname

        Dim p As New SqlParameter(paraname, value)
        p.SqlDbType = SqlDbType.NChar
        p.Size = size
        p.Direction = direction
        paras.Add(p, paraname)
    End Sub

    Public Sub addNText(ByVal paraname As String, ByVal value As Object, Optional ByVal direction As System.Data.ParameterDirection = System.Data.ParameterDirection.Input) Implements IParaHelper.addNText
        If Not paraname.StartsWith("@") Then paraname = "@" & paraname

        Dim p As New SqlParameter(paraname, value)
        p.SqlDbType = SqlDbType.NText
        p.Direction = direction
        paras.Add(p, paraname)
    End Sub

    Public Sub addNVarchar(ByVal paraname As String, ByVal value As Object, ByVal size As Integer, Optional ByVal direction As System.Data.ParameterDirection = System.Data.ParameterDirection.Input) Implements IParaHelper.addNVarchar
        If Not paraname.StartsWith("@") Then paraname = "@" & paraname

        Dim p As New SqlParameter(paraname, value)
        p.SqlDbType = SqlDbType.NVarChar
        p.Size = size
        p.Direction = direction
        paras.Add(p, paraname)
    End Sub

    Public Sub addSmallDateTime(ByVal paraname As String, ByVal value As Object, Optional ByVal direction As System.Data.ParameterDirection = System.Data.ParameterDirection.Input) Implements IParaHelper.addSmallDateTime
        If Not paraname.StartsWith("@") Then paraname = "@" & paraname
        Dim p As New SqlParameter(paraname, value)
        p.SqlDbType = SqlDbType.SmallDateTime
        p.Direction = direction
        paras.Add(p, paraname)
    End Sub

    Public Sub addText(ByVal paraname As String, ByVal value As Object, Optional ByVal direction As System.Data.ParameterDirection = System.Data.ParameterDirection.Input) Implements IParaHelper.addText
        If Not paraname.StartsWith("@") Then paraname = "@" & paraname

        Dim p As New SqlParameter(paraname, value)
        p.SqlDbType = SqlDbType.Text
        p.Direction = direction
        paras.Add(p, paraname)
    End Sub

    Public Sub addTinyInt(ByVal paraname As String, ByVal value As Object, Optional ByVal direction As System.Data.ParameterDirection = System.Data.ParameterDirection.Input) Implements IParaHelper.addTinyInt
        If Not paraname.StartsWith("@") Then paraname = "@" & paraname

        Dim p As New SqlParameter(paraname, value)
        p.Direction = direction
        p.SqlDbType = SqlDbType.TinyInt
        paras.Add(p, paraname)
    End Sub

    Public Sub addVarchar(ByVal paraname As String, ByVal value As Object, ByVal size As Integer, Optional ByVal direction As System.Data.ParameterDirection = System.Data.ParameterDirection.Input) Implements IParaHelper.addVarchar
        If Not paraname.StartsWith("@") Then paraname = "@" & paraname

        Dim p As New SqlParameter(paraname, value)
        p.SqlDbType = SqlDbType.VarChar
        p.Size = size
        p.Direction = direction
        paras.Add(p, paraname)
    End Sub

    Public Sub ClearParameters() Implements IParaHelper.ClearParameters
        paras = New Collection
    End Sub

    Public Function GetInsertSQL(ByVal tablename As String) As String Implements IParaHelper.GetInsertSQL
        Dim sql As New StringBuilder
        sql.Append("insert into " & tablename & " (")

        Dim i As Integer
        For i = 1 To Me.Parameters.Count - 1
            sql.Append("[" & Me.Parameters.Item(i).ParameterName.Substring(1) & "]")
            sql.Append(", ")
        Next
        sql.Append(Me.Parameters.Item(Me.Parameters.Count).ParameterName.Substring(1))
        sql.Append(")values(")

        For i = 1 To Me.Parameters.Count - 1
            sql.Append(Me.Parameters.Item(i).ParameterName)
            sql.Append(", ")
        Next
        sql.Append(Me.Parameters.Item(Me.Parameters.Count).ParameterName)
        sql.Append(")")

        Return sql.ToString
    End Function

    Public Function GetParameter(ByVal paraname As String) As Object Implements IParaHelper.GetParameter
        If Not paraname.StartsWith("@") Then paraname = "@" & paraname

        Dim p As SqlParameter
        Try
            p = paras.Item(paraname)
        Catch ex As ArgumentException
            p = Nothing
        End Try

        Return p
    End Function

    Public Function GetUpdateSQL(ByVal tablename As String, ByVal ParamArray keys() As String) As String Implements IParaHelper.GetUpdateSQL
        Dim sql As New StringBuilder
        sql.Append("update " & tablename & " set ")

        Dim i As Integer
        Dim s As String
        Dim hs As New Hashtable

        If keys.Length = 0 Then
            hs.Add("id", Nothing)
        End If
        For Each s In keys
            hs.Add(s, Nothing)
        Next

        For i = 1 To Me.Parameters.Count - 1
            s = Me.Parameters.Item(i).ParameterName.Substring(1)
            If Not hs.ContainsKey(s) Then
                sql.Append("[" & s & "]")
                sql.Append("=")
                sql.Append(Me.Parameters.Item(i).ParameterName)
                sql.Append(", ")
            End If
        Next

        s = Me.Parameters.Item(Me.Parameters.Count).ParameterName.Substring(1)

        If Not hs.ContainsKey(s) Then
            sql.Append("[" & Me.Parameters.Item(Me.Parameters.Count).ParameterName.Substring(1) & "]")
            sql.Append("=")
            sql.Append(Me.Parameters.Item(Me.Parameters.Count).ParameterName)
        Else
            sql.Remove(sql.Length - 2, 2)
        End If

        sql.Append(" where ")
        Dim ar As New ArrayList

        For Each s In hs.Keys
            ar.Add("[" & s & "]=@" & s)
        Next
        sql.Append(util.JoinStr(ar, " and "))

        Return sql.ToString
    End Function

    Public Property Parameters() As Microsoft.VisualBasic.Collection Implements IParaHelper.Parameters
        Get
            Return paras
        End Get
        Set(ByVal value As Microsoft.VisualBasic.Collection)
            paras = value
        End Set
    End Property
End Class
